/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package modelo.helper;
import java.util.ArrayList;
import java.util.Iterator;
import modelo.DTO.DTODataMining;
import modelo.DTO.DTOVisitasAlumno;
import modelo.HibernateUtil;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;

/**
 *
 * @version 1.1
 * @author Ion B. Padilla
 */
public class ForumHelper {
    private static ForumHelper instance;

    public ForumHelper() {
    }

    public static ForumHelper getInstance() {
        if (null == instance) {
            instance = new ForumHelper();
        }
        return instance;
    }
    /**
     * Metodo que devuelve el uso del foro
     */
    public ArrayList<DTODataMining> getForumPostedPorStudents(){
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery("SELECT userPost.username as poster, COUNT(userPost.username) as postnumb "
                    + "FROM MdlForumPosts post,MdlUser userPost "
                    + "WHERE post.userid=userPost.id "
                    + "GROUP BY userPost.username "
                    + "ORDER BY COUNT(userPost.username)").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                DTOVisitasAlumno alumno = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
                lista.add(alumno);
            }
            tx.commit(); session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return new ArrayList<DTODataMining>();
        }
    }
    /**
     * Metodo el uso del foro por curso
     * @param curso el curso del que queremos consultar
     */
    public ArrayList<DTODataMining> getForumPostedPorStudentsPorCurso(int curso){
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery("SELECT userPost.username as poster, COUNT(userPost.username) as postnumb "
                    + "FROM MdlForumPosts post,MdlUser userPost, MdlForumDiscussions disc, MdlUser userDisc "
                    + " WHERE post.userid=userPost.id "
                    + " AND disc.userid=userDisc.id"
                    + " AND post.discussion=disc.id"
                    + " AND disc.course="+curso
                    + " GROUP BY userPost.username "
                    + " ORDER BY COUNT(userPost.username)").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                DTOVisitasAlumno alumno = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
                lista.add(alumno);
            }
            tx.commit(); session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return new ArrayList<DTODataMining>();
        }
    }
    /**
     * Metodo el uso del foro por alumno
     * @param nombre el nombre del alumno
     */
    public DTODataMining getForumPostedPorStudent(String nombre) {
        DTOVisitasAlumno dtoVisitas = null;
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT userPost.username as poster, COUNT(userPost.username) as postnumb "
                    + " FROM Mdlforum_posts post "
                    + " INNER JOIN Mdluser userDisc "
                    + " ON disc.userid=userDisc.id "
                    + " WHERE userPost.username ='"+nombre+"' "
                    + " GROUP BY userPost.username "
                    + " ORDER BY COUNT(userPost.username) ").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoVisitas = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit(); session.close();
            return dtoVisitas;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return new DTOVisitasAlumno();
        }
    }
    /**
     * Metodo el uso del foro por alumno y curso
     * @param nombre el nombre del alumno
     * @param curso id del curso del que queremos consultar
     */
    public DTODataMining getForumPostedPorStudentPorCurso(String nombre,int curso) {
        DTOVisitasAlumno dtoVisitas = null;
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery("SELECT userPost.username as poster, COUNT(userPost.username) as postnumb "
                    + "FROM MdlForumPosts post,MdlUser userPost, MdlForumDiscussions disc, MdlUser userDisc "
                    + " WHERE post.userid=userPost.id "
                    + " AND disc.userid=userDisc.id"
                    + " AND post.discussion=disc.id"
                    + " AND disc.course="+curso+""
                    + " AND userPost.username='"+nombre+"' "
                    + " GROUP BY userPost.username "
                    + " ORDER BY COUNT(userPost.username)").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoVisitas = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit(); session.close();
            return dtoVisitas;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return new DTOVisitasAlumno();
        }
    }

}
